package com.suixuan.support.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * @Copyright (c) Nanjing Zhou Su co.,Ltd
 * 
 * @Project oa
 * @Title ExcelImport.java
 * @Package com.suixuan.support.util
 * @Description Excel导入
 * @Author suixuan
 * @Editors 1
 * @Date 2011-1-4 上午10:47:38
 * @Note
 * @Version:1.0.0
 * 
 */
public class ExcelImport {
	private HSSFWorkbook workBook;

	public ExcelImport(String filePath) throws ExcelException {
		super();
		workBook = this.readHSSFWorkbook(filePath);
	}

	public ExcelImport() {
		super();
	}

	/**
	 * 获取Excel中的工作簿
	 * 
	 * @Title readHSSFWorkbook
	 * @param filePath
	 *            文件路径
	 * @return {@link HSSFWorkbook}
	 * @throws ExcelException
	 * 
	 */
	public HSSFWorkbook readHSSFWorkbook(String filePath) throws ExcelException {
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
			workBook = new HSSFWorkbook(fs);
		} catch (IOException e) {
			throw new ExcelException(e);
		}
		return workBook;
	}

	/**
	 * 获取所有的工作簿
	 * 
	 * @Title getHSSFSheet
	 * @param hssfSheetMap
	 *            所有工作簿
	 * @return {@link Map}
	 * 
	 */
	public Map<Integer, HSSFSheet> getHSSFSheet(Map<Integer, HSSFSheet> hssfSheetMap) {
		for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
			// 创建工作表
			HSSFSheet sheet = workBook.getSheetAt(i);
			hssfSheetMap.put(i, sheet);
		}

		return hssfSheetMap;
	}

	/**
	 * 获取单个工作簿中的所有行
	 * 
	 * @Title getHssfRow
	 * @param hssfSheetMap
	 *            {@link Map}
	 * @param sheetIndex
	 *            工作簿索引
	 * @param hssfRows
	 *            {@link List}
	 * @return {@link List}
	 * 
	 */
	public List<HSSFRow> getHssfRow(Map<Integer, HSSFSheet> hssfSheetMap, int sheetIndex, List<HSSFRow> hssfRows) {
		HSSFSheet hssfSheet = hssfSheetMap.get(sheetIndex);
		if (null == hssfSheet) {
			return hssfRows;
		}
		// 获得行数
		int rows = hssfSheet.getPhysicalNumberOfRows();
		if (rows > 0) {
			hssfSheet.getMargin(HSSFSheet.TopMargin);
			// 行循环
			for (int r = 0; r < rows; r++) {
				hssfRows.add(hssfSheet.getRow(r));
			}
		}
		return hssfRows;
	}

	/**
	 * 获取行中的所有值
	 * 
	 * @Title getHssCellValues
	 * @param hssfRow
	 *            {@link HSSFRow}
	 * @param rowValues
	 *            {@link List}
	 * @return {@link List}
	 * 
	 */
	public List<String> getHssCellValues(HSSFRow hssfRow, List<String> rowValues) {
		// 获得列数
		int cells = hssfRow.getLastCellNum();
		for (int c = 0; c < cells; c++) {
			// 列循环
			HSSFCell cell = hssfRow.getCell(c);
			if (cell != null) {
				rowValues.add(this.getValue(cell));
			}
		}
		return rowValues;
	}

	/**
	 * 获取列中的值
	 * 
	 * @Title getValue
	 * @param cell
	 *            {@link HSSFCell}
	 * @return String值
	 * 
	 */
	private String getValue(HSSFCell cell) {
		String value = ConstantUtils.EMPTY_STRING;
		switch (cell.getCellType()) {
		// 数值型
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
			} else {
				// 纯数字
				value = String.valueOf(cell.getNumericCellValue());
			}
			break;
		/* 此行表示单元格的内容为string类型 */
		case HSSFCell.CELL_TYPE_STRING: // 字符串型
			value = cell.getRichStringCellValue().toString();
			break;
		// 公式型
		case HSSFCell.CELL_TYPE_FORMULA:
			// 读公式计算值
			value = String.valueOf(cell.getNumericCellValue());
			// 如果获取的数据值为非法值,则转换为获取字符串
			if ("NaN".equals(value)) {
				value = cell.getRichStringCellValue().toString();
			}
			// cell.getCellFormula();读公式
			break;
		// 布尔
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = " " + cell.getBooleanCellValue();
			break;
		/* 此行表示该单元格值为空 */
		// 空值
		case HSSFCell.CELL_TYPE_BLANK:
			value = ConstantUtils.EMPTY_STRING;
			break;
		// 故障
		case HSSFCell.CELL_TYPE_ERROR:
			value = ConstantUtils.EMPTY_STRING;
			break;
		default:
			value = cell.getRichStringCellValue().toString();
		}
		return value;
	}
	
	
	public static void main(String[] args) throws ExcelException {
		ExcelImport excelImport = new ExcelImport("c:/test.xls");
		Map<Integer, HSSFSheet> hssfSheetMap = new HashMap<Integer, HSSFSheet>();
		hssfSheetMap = excelImport.getHSSFSheet(hssfSheetMap);
		
		List<HSSFRow> hssfRows = excelImport.getHssfRow(hssfSheetMap, 0, new ArrayList<HSSFRow>());
		
		System.out.println(excelImport.getHssCellValues(hssfRows.get(1), new ArrayList<String>()));
	}
}
